Die Daten stammen aus dem FIS-Broker der Senatsverwaltung für Stadtentwicklung und Wohnen. Für die Jahre 2009 und 2010 fehlen aktuell die Attribute.
Zur Arbeit mit dem FIS-Broker siehe auch den Vortrag “Daten aus dem FIS-Broker” von Jochen Klar zum ODD 2017 in Berlin.
library("gdalUtils")
library("sp")
library("ggplot2")
suppressPackageStartupMessages(library("rgdal"))
suppressPackageStartupMessages(library("dplyr"))
library("leaflet")
library("viridis")
knitr::opts_chunk$set(echo = TRUE)read_brw <- function(year) {
dsn <- paste0("WFS:http://fbinter.stadt-berlin.de/fb/wfs/geometry/senstadt/re_brw", year)
dest <- paste0("./data/brw", year, ".shp")
fis <- paste0("fis:re_brw", year)
ogr2ogr(s_srs = "EPSG:25833",
t_srs = "WGS84",
f = "ESRI Shapefile",
dsn,
dest,
fis,
overwrite = TRUE)
}
read_shp <- function(x) {
a1 <- paste0("./data/brw", x, ".shp")
a2 <- paste0("brw", x)
z <- readOGR(a1, a2, stringsAsFactors = FALSE)
z@data
}Map(read_brw, 2002:2017)dat <- Map(read_shp, 2002:2017) %>%
bind_rows() %>%
readr::type_convert() %>%
mutate(year = as.numeric(format(STICHTAG, "%Y"))) %>%
arrange(spatial_na, year)## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2002.shp", layer: "brw2002"
## with 1035 features
## It has 11 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2003.shp", layer: "brw2003"
## with 1036 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2004.shp", layer: "brw2004"
## with 1034 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2005.shp", layer: "brw2005"
## with 1038 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2006.shp", layer: "brw2006"
## with 1009 features
## It has 11 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2007.shp", layer: "brw2007"
## with 1062 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2008.shp", layer: "brw2008"
## with 1060 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2009.shp", layer: "brw2009"
## with 1071 features
## It has 4 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2010.shp", layer: "brw2010"
## with 1088 features
## It has 4 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2011.shp", layer: "brw2011"
## with 1093 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2012.shp", layer: "brw2012"
## with 1097 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2013.shp", layer: "brw2013"
## with 1153 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2014.shp", layer: "brw2014"
## with 1161 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2015.shp", layer: "brw2015"
## with 1145 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2016.shp", layer: "brw2016"
## with 1136 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2017.shp", layer: "brw2017"
## with 1142 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
## Parsed with column specification:
## cols(
## gml_id = col_character(),
## spatial_na = col_integer(),
## spatial_al = col_integer(),
## spatial_ty = col_character(),
## BEZIRK = col_character(),
## ANWERT = col_character(),
## NUTZUNG = col_character(),
## STICHTAG = col_datetime(format = ""),
## BEITRAGSZU = col_character(),
## LUMNUM = col_character(),
## VERFAHRENS = col_character(),
## GEMEINDE = col_character()
## )
glimpse(dat)## Observations: 17,360
## Variables: 15
## $ gml_id <chr> "re_brw2009.0", "re_brw2010.0", "re_brw2010.0", "re...
## $ spatial_na <int> 0, 0, 0, 0, 5, 1000, 1000, 1000, 1000, 1000, 1001, ...
## $ spatial_al <int> 0, 0, 0, 0, 5, 1000, 1000, 1000, 1000, 1000, 1001, ...
## $ spatial_ty <chr> "Polygon", "Polygon", "Polygon", "Polygon", "Polygo...
## $ BEZIRK <chr> NA, NA, NA, NA, NA, "Marzahn-Hellersdorf", "Marzahn...
## $ ANWERT <chr> NA, NA, NA, NA, NA, "A", "A", "A", "A", "A", NA, NA...
## $ BRW <dbl> NA, NA, NA, NA, NA, 160, 140, 120, 100, 80, 180, 16...
## $ NUTZUNG <chr> NA, NA, NA, NA, NA, "G - Gewerbe", "G - Gewerbe", "...
## $ STICHTAG <dttm> NA, NA, NA, NA, NA, 2002-01-01, 2003-01-01, 2004-0...
## $ BEITRAGSZU <chr> NA, NA, NA, NA, NA, "Beitragsfrei nach BauGB", "Bei...
## $ LUMNUM <chr> NA, NA, NA, NA, NA, "[[url]]", "[[url]]", "[[url]]"...
## $ VERFAHRENS <chr> NA, NA, NA, NA, NA, NA, "Entw", "Entw", "Entw", "En...
## $ GFZ <dbl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,...
## $ GEMEINDE <chr> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,...
## $ year <dbl> NA, NA, NA, NA, NA, 2002, 2003, 2004, 2005, 2007, 2...
table(dat$BEZIRK, dat$year) %>% knitr::kable()| 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Charlottenburg-Wilmersdorf | 70 | 70 | 70 | 74 | 70 | 78 | 76 | 77 | 78 | 80 | 79 | 79 | 80 | 80 |
| Friedrichshain-Kreuzberg | 44 | 43 | 42 | 44 | 37 | 37 | 38 | 40 | 39 | 37 | 36 | 36 | 33 | 34 |
| Lichtenberg | 75 | 74 | 72 | 73 | 70 | 71 | 71 | 73 | 75 | 82 | 83 | 83 | 82 | 81 |
| Marzahn-Hellersdorf | 46 | 49 | 49 | 48 | 48 | 52 | 55 | 56 | 58 | 57 | 57 | 58 | 57 | 57 |
| Mitte | 92 | 92 | 93 | 91 | 80 | 84 | 89 | 91 | 92 | 93 | 96 | 92 | 92 | 95 |
| Neukölln | 59 | 59 | 59 | 61 | 60 | 60 | 60 | 62 | 63 | 64 | 64 | 64 | 64 | 64 |
| Pankow | 122 | 122 | 121 | 122 | 121 | 112 | 116 | 124 | 121 | 129 | 131 | 129 | 126 | 125 |
| Reinickendorf | 95 | 95 | 93 | 92 | 92 | 97 | 96 | 100 | 99 | 108 | 106 | 104 | 103 | 104 |
| Spandau | 122 | 122 | 125 | 124 | 122 | 125 | 129 | 137 | 139 | 147 | 148 | 149 | 149 | 147 |
| Steglitz-Zehlendorf | 112 | 112 | 114 | 114 | 106 | 113 | 114 | 122 | 121 | 125 | 126 | 125 | 125 | 127 |
| Tempelhof-Schöneberg | 69 | 69 | 68 | 68 | 61 | 69 | 69 | 70 | 70 | 73 | 74 | 72 | 73 | 74 |
| Treptow-Köpenick | 128 | 129 | 128 | 127 | 131 | 131 | 135 | 141 | 142 | 158 | 161 | 154 | 152 | 154 |
table(dat$NUTZUNG, dat$year) %>% knitr::kable()| 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| F - Forstflächen | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 35 | 35 | 35 | 35 | 35 |
| G - Gewerbe | 234 | 234 | 234 | 228 | 234 | 242 | 243 | 238 | 238 | 236 | 235 | 227 | 224 | 223 |
| GB - Gemeinbedarf | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 34 | 35 | 37 | 38 | 39 | 39 | 39 |
| Gp - Gewerbe produzierend | 18 | 18 | 17 | 18 | 15 | 10 | 9 | 7 | 6 | 4 | 4 | 4 | 4 | 4 |
| LW-A - Acker | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | 10 | 11 | 11 | 11 |
| LW-EGA - Erwerbsgartenbau | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 4 | 3 | 3 | 3 |
| LW-G - Grünland | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 0 | 0 | 0 | 0 |
| LW-GR - Grünland | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 6 | 6 | 6 |
| M1 - Kerngebiet | 54 | 54 | 54 | 52 | 35 | 49 | 52 | 54 | 54 | 56 | 59 | 55 | 53 | 53 |
| M1* - Einzelhandelszentrum | 0 | 0 | 0 | 0 | 12 | 14 | 15 | 16 | 16 | 15 | 15 | 15 | 8 | 8 |
| M2 - Mischgebiet | 48 | 49 | 50 | 48 | 19 | 43 | 43 | 44 | 45 | 43 | 41 | 39 | 37 | 39 |
| W - Wohngebiet | 680 | 681 | 679 | 692 | 683 | 671 | 686 | 700 | 703 | 707 | 714 | 711 | 716 | 721 |
z <- dat %>%
group_by(BEZIRK, NUTZUNG, GFZ, year) %>%
summarise(count = n(),
q25 = quantile(BRW, 0.25, na.rm = TRUE),
q75 = quantile(BRW, 0.75, na.rm = TRUE),
q95 = quantile(BRW, 0.75, na.rm = TRUE),
iqr = IQR(BRW, na.rm = TRUE),
mean = mean(BRW, na.rm = TRUE),
med = median(BRW, na.rm = TRUE)) %>%
filter(!is.na(BEZIRK)) %>%
ungroup()W - Wohngebietp0 <- ggplot(filter(z, year >= 2011, NUTZUNG == "W - Wohngebiet"),
aes(year, mean, colour = "BEZIRK", group = "BEZIRK"))
p0 <- p0 + geom_line()
p0 <- p0 + facet_grid(as.factor(GFZ) ~ BEZIRK, scales = "free")
p0 <- p0 + theme(axis.text.x = element_text(angle=90, vjust=0.5, size = 8),
axis.text.y = element_text(size = 8),
legend.position="none")
p0 <- p0 + labs(x = "",
y = "Durchschnitt in €",
title = "Bodenrichtwerte in der Nutzung 'W - Wohngebiet' (2011-2017)",
subtitle = "",
caption = "Quelle: FIS-Broker / Gutachterausschuss für Grundstückswerte in Berlin")
p0M2 - Mischgebietp1 <- ggplot(filter(z, year >= 2011, NUTZUNG == "M2 - Mischgebiet"),
aes(year, mean, colour = "BEZIRK", group = "BEZIRK"))
p1 <- p1 + geom_line()
p1 <- p1 + facet_grid(as.factor(GFZ) ~ BEZIRK, scales = "free")
p1 <- p1 + theme(axis.text.x = element_text(angle=90, vjust=0.5, size = 8),
axis.text.y = element_text(size = 8),
legend.position="none")
p1 <- p1 + labs(x = "",
y = "Durchschnitt in €",
title = "Bodenrichtwerte in der Nutzung 'M2 - Mischgebiet' (2011-2017)",
subtitle = "",
caption = "Quelle: FIS-Broker / Gutachterausschuss für Grundstückswerte in Berlin")
p1brw <- readOGR("./data/brw2017.shp", "brw2017",
stringsAsFactors = FALSE,
encoding = "UTF-8")## OGR data source with driver: ESRI Shapefile
## Source: "./data/brw2017.shp", layer: "brw2017"
## with 1142 features
## It has 12 fields
## Integer64 fields read as strings: spatial_na spatial_al
brw <- brw[brw$BRW <= 8000, ]
pal <- colorNumeric(
palette = viridis_pal()(10),
domain = brw@data$BRW
)
popup <- paste0("<b>", brw@data$spatial_al, " - ",
brw@data$NUTZUNG , " - ",
brw@data$GFZ, " - ",
brw@data$BRW, " Euro/qm")
leafMap <- leaflet(height = "800px", width = "800px") %>%
setView(lng = 13.383, lat = 52.516, zoom = 11) %>%
addProviderTiles(providers$CartoDB.Positron) %>%
addPolygons(data = brw,
stroke = TRUE,
dashArray = 1,
weight = 1.5,
color = "white",
smoothFactor = 0.20,
fillOpacity = 0.60,
fillColor = ~pal(brw@data$BRW),
popup = popup,
group = "Bodenrichtwerte") %>%
addLegend("bottomright",
pal = pal,
values = brw@data$BRW,
title = "Euro/qm",
labFormat = labelFormat(suffix = " "),
opacity = 1)leafMapdevtools::session_info()## Session info --------------------------------------------------------------
## setting value
## version R version 3.3.2 Patched (2016-12-01 r71715)
## system x86_64, mingw32
## ui RTerm
## language (EN)
## collate German_Germany.1252
## tz Europe/Berlin
## date 2017-03-07
## Packages ------------------------------------------------------------------
## package * version date source
## assertthat 0.1 2013-12-06 CRAN (R 3.3.2)
## backports 1.0.5 2017-01-18 CRAN (R 3.3.2)
## codetools 0.2-15 2016-10-05 CRAN (R 3.3.2)
## colorspace 1.3-2 2016-12-14 CRAN (R 3.3.2)
## crosstalk 1.0.1 2017-01-26 Github (rstudio/crosstalk@6a36da1)
## DBI 0.5-1 2016-09-10 CRAN (R 3.3.2)
## devtools 1.12.0 2016-06-24 CRAN (R 3.3.2)
## digest 0.6.12 2017-01-27 CRAN (R 3.3.2)
## dplyr * 0.5.0 2016-06-24 CRAN (R 3.3.2)
## evaluate 0.10 2016-10-11 CRAN (R 3.3.2)
## foreach 1.4.3 2015-10-13 CRAN (R 3.3.2)
## gdalUtils * 2.0.1.7 2015-10-10 CRAN (R 3.3.2)
## ggplot2 * 2.2.1.9000 2017-03-03 Github (tidyverse/ggplot2@fa8004e)
## gridExtra 2.2.1 2016-02-29 CRAN (R 3.3.2)
## gtable 0.2.0 2016-02-26 CRAN (R 3.3.2)
## highr 0.6 2016-05-09 CRAN (R 3.3.2)
## hms 0.3 2016-11-22 CRAN (R 3.3.2)
## htmltools 0.3.5 2016-03-21 CRAN (R 3.3.2)
## htmlwidgets 0.8 2016-12-17 Github (ramnathv/htmlwidgets@3ce9f90)
## httpuv 1.3.3 2015-08-04 CRAN (R 3.3.2)
## iterators 1.0.8 2015-10-13 CRAN (R 3.3.2)
## jsonlite 1.3 2017-02-28 CRAN (R 3.3.2)
## knitr 1.15.1 2016-11-22 CRAN (R 3.3.2)
## labeling 0.3 2014-08-23 CRAN (R 3.3.2)
## lattice 0.20-34 2016-09-06 CRAN (R 3.3.2)
## lazyeval 0.2.0.9000 2016-12-19 Github (hadley/lazyeval@c155c3d)
## leaflet * 1.1.0 2017-02-21 CRAN (R 3.3.2)
## magrittr 1.5 2014-11-22 CRAN (R 3.3.2)
## memoise 1.0.0 2016-01-29 CRAN (R 3.3.2)
## mime 0.5 2016-07-07 CRAN (R 3.3.2)
## munsell 0.4.3 2016-02-13 CRAN (R 3.3.2)
## plyr 1.8.4 2016-06-08 CRAN (R 3.3.2)
## R.methodsS3 1.7.1 2016-02-16 CRAN (R 3.3.2)
## R.oo 1.21.0 2016-11-01 CRAN (R 3.3.2)
## R.utils 2.5.0 2016-11-07 CRAN (R 3.3.2)
## R6 2.2.0 2016-10-05 CRAN (R 3.3.2)
## raster 2.5-8 2016-06-02 CRAN (R 3.3.2)
## Rcpp 0.12.9 2017-01-14 CRAN (R 3.3.2)
## readr 1.0.0.9000 2017-02-12 Github (tidyverse/readr@5f9fd71)
## reshape2 1.4.2 2016-10-22 CRAN (R 3.3.2)
## rgdal * 1.2-5 2016-12-15 CRAN (R 3.3.2)
## rmarkdown 1.3 2016-12-21 CRAN (R 3.3.2)
## rprojroot 1.2 2017-01-16 CRAN (R 3.3.2)
## scales 0.4.1 2016-11-09 CRAN (R 3.3.2)
## shiny 1.0.0 2017-01-12 CRAN (R 3.3.2)
## sp * 1.2-4 2017-01-04 Github (edzer/sp@8507a7b)
## stringi 1.1.2 2016-10-01 CRAN (R 3.3.2)
## stringr 1.2.0 2017-02-18 CRAN (R 3.3.2)
## tibble 1.2 2016-08-26 CRAN (R 3.3.2)
## viridis * 0.3.4 2016-12-03 Github (sjmgarnier/viridis@e54d97f)
## withr 1.0.2 2016-06-20 CRAN (R 3.3.2)
## xtable 1.8-2 2016-02-05 CRAN (R 3.3.2)
## yaml 2.1.14 2016-11-12 CRAN (R 3.3.2)